package com.zxl.manager.dao.impl;

import com.zxl.manager.dao.CommDao;
import com.zxl.manager.entity.Comm;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by jimmy on 2017/7/16.
 */
@Repository
public class CommDaoImpl implements CommDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Comm> getAllConn(Integer limit,Integer offset){
        String sql = "select a.comm_id,b.ord_id,d.product_name,e.`value`,b.prod_price,b.prod_sum,a.comm_value,a.create_time,c.nick_name from commission a LEFT JOIN wh_order b on a.ord_id = b.ord_id " +
                "LEFT JOIN wx_user c on a.user_id = c.open_id " +
                "LEFT JOIN product d on b.prod_id = d.product_id " +
                "LEFT JOIN product_detail e on b.spec_id = e.detail_id " +
                "ORDER BY COMM_ID DESC LIMIT ?,?";
        List<Comm> list = jdbcTemplate.query(sql,new Object[]{offset,limit}, new RowMapper<Comm>() {
            @Override
            public Comm mapRow(ResultSet resultSet, int i) throws SQLException {
                Comm comm = new Comm();
                comm.setCommId(resultSet.getLong(1));
                comm.setOrdId(resultSet.getLong(2));
                comm.setProdName(resultSet.getString(3));
                comm.setSpec(resultSet.getString(4));
                comm.setProdPrice(resultSet.getDouble(5));
                comm.setProdSum(resultSet.getString(6));
                comm.setCommValue(resultSet.getDouble(7));
                comm.setCreateTime(resultSet.getTimestamp(8));
                comm.setUserName(resultSet.getString(9));
                return comm;
            }
        });
        return list;
    }

    public int getCommCnt(){
        String sql = "select count(1) from commission a LEFT JOIN wh_order b on a.ord_id = b.ord_id " +
                "LEFT JOIN wx_user c on a.user_id = c.open_id " +
                "LEFT JOIN product d on b.prod_id = d.product_id " +
                "LEFT JOIN product_detail e on b.spec_id = e.detail_id ";
        int cnt = jdbcTemplate.query(sql,new RowMapper<Integer>(){

            @Override
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                int cnt = resultSet.getInt(1);
                return cnt;
            }
        }).get(0);

        return cnt;
    }

    public List<Map<String,String>> getLevels(){
        String sql = "select data_name,data_value from static_data where data_type = 'COMM'";
        List<Map<String,String>> res = jdbcTemplate.query(sql, new RowMapper<Map<String, String>>() {
            @Override
            public Map<String, String> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, String> resMap = new HashMap<>();
                resMap.put(resultSet.getString(1),resultSet.getString(2));
                return resMap;
            }
        });
        return res;
    }

    public int updComm(String level1,String level2){
        String sql = "update static_data set data_value=? where data_type='COMM' and data_name=?";
        int r = jdbcTemplate.update(sql,new Object[]{level1,"LEVEL1"});
        r = jdbcTemplate.update(sql,new Object[]{level2,"LEVEL2"});
        return r;
    }

    public int insCommRel(String parentUserId,String userId){
        String sql = "insert into commission_rel(parent_user_id,user_id) values(?,?)";
        int res = jdbcTemplate.update(sql,new Object[]{parentUserId,userId});
        return res;
    }

}
